--创建病历表序列
create sequence seq_medical_history;

--创建病历表
create table medical_history
(

    id                   number(8) primary key,--序列。主键

    medical_history_num  nvarchar2(50) default 'medical_history_' || substr(sys_guid(), 1, 12) not null unique,--病历编号，非空，唯一。默认表名_uuid()前12位。

    diagnosis_result     varchar2(200),--诊断结果。

    medicine_info        nvarchar2(300),--用药信息。

    patient_id           number(8),--病患id。

    physician_id         number(8),--主治医师id。

    sickbed_id           number(8),--病床id。

    nurse_id             number(8),--护士id(用于照顾住院的病人)。

    is_enable            number(1)     default 1, -- 是否可用，0：不可用，1：可用 , 默认1

    create_date          date          default sysdate, -- 创建时间

    create_username      nvarchar2(50), -- 创建用户名称

    last_update_date     date, -- 最后一次修改时间

    last_update_username nvarchar2(50), -- 最后一次修改用户名称

    delete_date          date, -- 删除时间

    constraint fk_patient_id foreign key (patient_id) references patients (id),--外键约束（patient_id）

    constraint fk_physician_id foreign key (physician_id) references medical (id),--外键约束（physician_id）

    constraint fk_sickbed_id foreign key (sickbed_id) references sickbed (id),--外键约束（sickbed_id）

    constraint fk_nurse_id foreign key (nurse_id) references medical (id)--外键约束（nurse_id）
);

-- insert into medical_history(id,diagnosis_result,medicine_info,patient_id,physician_id,sickbed_id,nurse_id)
-- values
-- (seq_medical_history.nextval,'流行感冒','连花清瘟胶囊',2,2,8,null);

-- insert into medical_history(id,diagnosis_result,medicine_info,patient_id,physician_id,sickbed_id,nurse_id)
-- values
-- (seq_medical_history.nextval,'流行感冒','连花清瘟胶囊',3,2,8,null);

insert into medical_history(id, diagnosis_result, medicine_info, patient_id, physician_id, sickbed_id, nurse_id)
values (seq_medical_history.nextval, '发炎', '头孢', 4, 3, 9, null);

insert into medical_history(id, diagnosis_result, medicine_info, patient_id, physician_id, sickbed_id, nurse_id)
values (seq_medical_history.nextval, '发炎', '头孢', 5, 3, 9, null);

insert into medical_history(id, diagnosis_result, medicine_info, patient_id, physician_id, sickbed_id, nurse_id)
values (seq_medical_history.nextval, '脑瘤', '抗瘤用药', 6, 9, 25, null);


select *
from medical_history;

commit;
